package com.wat4m.jerome.jdpcpool.service;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.wat4m.jerome.jdpcpool.po.Book;
import com.wat4m.jerome.jdpcpool.util.C3P0Util;
import com.wat4m.jerome.jdpcpool.util.DBCPUtil;

public class BookService {
	public List<Book> findAll() throws SQLException{
		List<Book> result=new ArrayList<Book>();
		String sql = "select * from book";
		Connection con=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		try {
//			con = C3P0Util.getConnection();
			con = DBCPUtil.getConnection();
			ps= con.prepareStatement(sql);
			rs = ps.executeQuery();
			while(rs.next()){
				Book b=new Book();
				b.setId(rs.getInt(1));
				b.setName(rs.getString(2));
				result.add(b);
			}
		} catch (SQLException e) {
			throw e;
		}finally{
			if(rs!=null)
			rs.close();
			if(ps!=null)
			ps.close();
			if(con!=null)
			con.close();
		}
		return result;
	}
	public boolean save(Book b) throws SQLException{
		Connection con = C3P0Util.getConnection();
		String sql = "insert into book(name) values(?)";
		PreparedStatement ps = con.prepareStatement(sql);
		ps.setString(0, b.getName());
		return ps.execute();
	}
}
